﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Web;
using up7.filemgr.app;

namespace up7.db.sql
{
    /// <summary>
    /// 条件
    /// SqlWhere.build().eq().min().max().like()
    /// </summary>
    public class SqlWhere
    {
        /// <summary>
        /// 字段列表，默认判断操作=,a=@a
        /// </summary>
        Dictionary<string, SqlParam> m_params;
        /// <summary>
        /// 针对oralce提供的空字符串查询变量
        /// </summary>
        Dictionary<string, SqlParam> m_nvls;
        /// <summary>
        /// 条件SQL语句
        /// </summary>
        Dictionary<string, string> m_sqls;

        public SqlWhere()
        {
            this.m_params = new Dictionary<string, SqlParam>();
            this.m_nvls = new Dictionary<string, SqlParam>();
            this.m_sqls = new Dictionary<string, string>();
        }
        public static SqlWhere build() { return new SqlWhere(); }

        /// <summary>
        /// 增加SQL语句
        /// </summary>
        /// <param name="n"></param>
        /// <param name="v"></param>
        /// <returns></returns>
        public SqlWhere sql(string n, string v)
        {
            if (string.IsNullOrEmpty(v)) return this;
            if (this.m_sqls.ContainsKey(n))
            {
                this.m_sqls[n] = v;
            }
            else this.m_sqls.Add(n, v);
            return this;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name">字段名称</param>
        /// <param name="value">字段值</param>
        public SqlWhere eq(string name, string value)
        {
            this.m_params.Add(name, new SqlParam(name, value));
            return this;
        }
        public SqlWhere ineq(string name, string value)
        {
            this.m_params.Add(name, new SqlParam(name, value, "!="));
            return this;
        }
        public SqlWhere eq(string name, bool value)
        {
            this.m_params.Add(name, new SqlParam(name, value));
            return this;
        }
        public SqlWhere nvl(string name, string value)
        {
            this.m_nvls.Add(name, new SqlParam(name, string.IsNullOrEmpty(value) ? " " : value));
            return this;
        }
        public SqlWhere ineq(string name, bool value)
        {
            this.m_params.Add(name, new SqlParam(name, value, "!="));
            return this;
        }

        public SqlWhere eq(string name, int value)
        {
            this.m_params.Add(name, new SqlParam(name, value));
            return this;
        }

        /// <summary>
        /// 转换为条件声明语句：name=@name and age=@age
        /// </summary>
        /// <returns></returns>
        public string toSql(string op = "and")
        {
            List<string> sqls = new List<string>();

            //sql参数转成条件
            foreach (var p in m_params)
            {
                //a=@a,a=:a
                sqls.Add(string.Format("{0}{1}{2}", p.Key, p.Value.pre, p.Value.ParamterName));
            }
            //针对oracle的空字符串判断
            foreach (var p in this.m_nvls)
            {
                //nvl(column,' ')= :column
                sqls.Add(string.Format("nvl({0},' ')={1}", p.Key, p.Value.ParamterName));
            }
            //添加sql语句
            foreach (var a in this.m_sqls)
            {
                sqls.Add(a.Value);
            }
            var sql = string.Join(" " + op + " ", sqls.ToArray());
            return sql;
        }

        /// <summary>
        /// 转换为完整条件语句：where a=@a and b=@b and c=@c
        /// </summary>
        /// <param name="op"></param>
        /// <returns></returns>
        public string toSqlWhere(string op = "and")
        {
            if (this.empty()) return "";
            return " where " + this.toSql(op);
        }

        public SqlParam[] toArray()
        {
            List<SqlParam> sqls = new List<SqlParam>();
            foreach (var a in this.m_params)
            {
                sqls.Add(a.Value);
            }
            return sqls.ToArray();
        }

        public bool empty()
        {
            return this.m_params.Count == 0 && this.m_sqls.Count == 0;
        }

        /// <summary>
        /// 创建变量并将值绑定到cmd
        /// </summary>
        /// <param name="table"></param>
        /// <param name="cmd"></param>
        public void bind(SqlTable table, DbCommand cmd)
        {
            List<SqlParam> ps = new List<SqlParam>();
            foreach (var p in this.m_params) ps.Add(p.Value);
            foreach (var p in this.m_nvls) ps.Add(p.Value);
            var vals = table.mergeVal(ps.ToArray());

            foreach (var v in vals)
            {
                v.bind(cmd);
            }
        }
    }
}